package com.coupons.business.merchantoffers.dao;

import java.util.List;

import com.coupons.business.common.exception.BaseCouponsAppException;
import com.coupons.business.common.vo.MerchantOffer;

public interface IMerchantOffersDAO 
{
	public void createMerchantOffers(MerchantOffer objaOffer) throws BaseCouponsAppException;
	public void updateMerchantOffers(MerchantOffer objaOffer) throws BaseCouponsAppException;
	public MerchantOffer getMerchantOffers(int OfferId) throws BaseCouponsAppException;
	public List<MerchantOffer> getAllMerchantOffers()throws BaseCouponsAppException ;
	
	public List<MerchantOffer> getOffersByMerchant(int merchantId)throws BaseCouponsAppException ;
	
	public List<MerchantOffer> getOffersByCategory(int partnerId, String categoryName)throws BaseCouponsAppException ;
	
	public List<MerchantOffer> getHomePageOffers(int partnerId)throws BaseCouponsAppException ;
	
	public List<MerchantOffer> getSpecialPageOffers(int partnerId)throws BaseCouponsAppException ;
	
	public List<MerchantOffer> getOffersBySearchKey(int partnerId, String searchKey)throws BaseCouponsAppException ;
	
	
	
	public String MERCHANTOFFERS_INSERT_STATEMENT = "INSERT INTO MERCHANT_OFFERS(OFFER_ID,OFFER_MORE_TXT,OFFER_MSG,EXPIRY_DATE," +
											  "COUPON_CODE,CTGRY_ID,HOME_PG_OFFER,HOME_PG_OOFER_INDEX,SPECIAL_OOFER,SPECIAL_OFFER_NDEX," +
											  "OFFER_REDIRECT_URL,OFFER_INDEX,LAST_UPDT_USER,PARTNER_ID,MRCHNT_ID," +
											  "CRTN_DT,LAST_UPDT_DT) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE,SYSDATE)";
	
	public String MERCHANTOFFERS_UPDATE_STATEMENT = "UPDATE MERCHANT_OFFERS SET OFFER_MORE_TXT = ?, OFFER_MSG = ?, EXPIRY_DATE = ?, " +
												"COUPON_CODE = ?, HOME_PG_OFFER=?, HOME_PG_OOFER_INDEX=?, SPECIAL_OOFER=?, " +
												" SPECIAL_OFFER_NDEX=?, " +" OFFER_REDIRECT_URL=?, OFFER_INDEX=?, " +
												"LAST_UPDT_USER=?, LAST_UPDT_DT=SYSDATE WHERE OFFER_ID = ?";
	
	public String SEQUENCE_NAME="MERCHANT_OFFERS_SQ";
	
	public String GET_ALL_MERCHANTOFFERS_STATEMENT = "SELECT * FROM MERCHANT_OFFERS";
	
	public String GET_MERCHANTOFFERS_STATEMENT = "SELECT * FROM MERCHANT_OFFERS where OFFER_ID = ?";
	
	public String GET_OFFERS_BY_CATEGORY_STATEMENT = "SELECT offer_id, offer_more_txt, offer_msg, expiry_date,coupon_code, ctgry_id, " +
													 "home_pg_offer, home_pg_oofer_index,special_oofer, special_offer_ndex, offer_redirect_url," +
													 "offer_index, mo.crtn_dt crtn_dt, mo.last_updt_dt last_updt_dt,mo.last_updt_user last_updt_user," +
													 "mo.mrchnt_id, mo.partner_id, merchant_emblem merchantEmblem  " +
													 "FROM MERCHANT_OFFERS MO, MERCHANT M WHERE MO.PARTNER_ID = ? " +
														" AND CTGRY_ID IN " +
														"(SELECT CTGRY_ID FROM CATEGORY WHERE  CTGRY_NAME = ?) " +
														" AND mo.mrchnt_id=m.mrchnt_id";
	public String GET_HOME_PAGE_OFFERS_STATEMENT = "SELECT offer_id, offer_more_txt, offer_msg, expiry_date,coupon_code, ctgry_id, " +
													"home_pg_offer, home_pg_oofer_index,special_oofer, special_offer_ndex, offer_redirect_url," +
													"offer_index, mo.crtn_dt crtn_dt, mo.last_updt_dt last_updt_dt,mo.last_updt_user last_updt_user," +
													"mo.mrchnt_id, mo.partner_id, merchant_emblem merchantEmblem  " +
													"FROM MERCHANT_OFFERS MO, MERCHANT M WHERE MO.HOME_PG_OFFER = 'yes' " +
													" AND (MO.EXPIRY_DATE>=SYSDATE OR MO.EXPIRY_DATE IS NULL) "+
													"  AND MO.PARTNER_ID = ?   AND mo.mrchnt_id=m.mrchnt_id " +
													"ORDER BY HOME_PG_OOFER_INDEX, LAST_UPDT_DT DESC";
	
	public String GET_SPECIAL_PAGE_OFFERS_STATEMENT = "SELECT * FROM MERCHANT_OFFERS WHERE SPECIAL_OOFER = 'yes' AND PARTNER_ID = ?";
	
	public String GET_OFFERS_BY_MERCHANT_STATEMENT = "select offer_id, offer_more_txt, offer_msg, expiry_date,coupon_code, " +
													"ctgry_id, home_pg_offer, home_pg_oofer_index,special_oofer, special_offer_ndex, " +
													"offer_redirect_url,offer_index, mo.crtn_dt crtn_dt, mo.last_updt_dt last_updt_dt," +
													"mo.last_updt_user last_updt_user,mo.mrchnt_id, mo.partner_id, " +
													"merchant_emblem merchantEmblem " +
													"  from merchant_offers mo, merchant m where mo.mrchnt_id=? and mo.mrchnt_id=m.mrchnt_id";



	public String GET_OFFERS_BY_SEARCHKEY = "SELECT offer_id, offer_more_txt, offer_msg, expiry_date,coupon_code, " +
											" ctgry_id, home_pg_offer, home_pg_oofer_index,special_oofer, " +
											" special_offer_ndex, offer_redirect_url, offer_index, mo.crtn_dt crtn_dt, " +
											" mo.last_updt_dt last_updt_dt,mo.last_updt_user last_updt_user, mo.mrchnt_id, " +
											" mo.partner_id, merchant_emblem merchantEmblem  " +
											" FROM MERCHANT_OFFERS MO, MERCHANT M WHERE MO.PARTNER_ID = ?  AND mo.mrchnt_id=m.mrchnt_id " +
											" AND( CTGRY_ID IN (SELECT CTGRY_ID FROM CATEGORY WHERE  UPPER(CTGRY_NAME) like '%" ;
	
	public String GET_OFFERS_BY_SEARCHKEY2 = "%')OR m.mrchnt_id IN ( SELECT mrchnt_id FROM MERCHANT WHERE UPPER(MRCHNT_NAME) LIKE '%";
	
											
	public String GET_OFFERS_BY_SEARCHKEY3 = "%') OR ";
	
	public String GET_OFFERS_BY_SEARCHKEY4 = " ( UPPER(mo.offer_redirect_url) like '%";
	
	public String GET_OFFERS_BY_SEARCHKEY5 = " %' ) )";
	
}

